import pymysql
import csv
import random
import json
import time
import collections

NTRITION_TYPE = ["DBZ", "WA", "WPP", "XIN", "ZF", "WE", "WC", "XI", "TL", "WB1", "GAI", "SSXW", "NL", "WB2", "TIE",
                 "DGC", "DWXDB", "DDDB", "DWXZF", "ZTNL", "DWXWA"]
# A1_NURTIION  =[]
NTRITION_STR = "DBZ,WA,WPP,XIN,ZF,WE,WC," \
               "XI,TL,WB1,GAI,SSXW,NL,WB2,TIE,DGC,DWXDB,DDDB,DWXZF,ZTNL,DWXWA"  # ensure consistency
IGNORE = 0
NTRITION = ["DBZ", "WA", "XIN", "ZF", "WE", "WC", "XI", "TL", "WB1", "GAI", "NL", "WB2", "TIE", "DGC"]
NTRITION_A1 = [90, 1000, 15, IGNORE, 10, 60, 50, IGNORE, 1.5, 800, (2600, 3000), 1.3, 15, IGNORE]

RECIPE_MAP = {

    "D1": [10, 1],
    "D2": [3, 1],
    "D3": [50, 1],
    "D4": [52, 1],
    "D5": [2, 2],
    "D6": [58, 4],
    "D7": [14, 1],
    "D8": [3, 1],
    "D9": [10, 1],
    "D10": [159, 7],
    "D11": [196, 1],
    "D12": [174, 4],
    "D13": [161, 2],
    "D14": [70, 2],
    "D15": [51, 4],

}


def connectdb():
    db = pymysql.connect(
        host="127.0.0.1",
        port=3306,
        user="root",
        password="123456",
        database="sys",
        charset="utf8"
    )
    return db


def closedb(db):
    db.close()


def create_table_nutrition():
    db = connectdb()
    cursor = db.cursor()
    cursor.execute("DROP TABLE IF EXISTS nutrition")
    sql = """
        CREATE TABLE nutrition (
            CP_ID varchar(45),
            CP_NAME varchar(45),
            DBZ varchar(45),
            WA varchar(45),
            WPP varchar(45),
            XIN varchar(45),
            ZF varchar(45),
            WE varchar(45),
            WC varchar(45),
            XI varchar(45),
            TL varchar(45),
            WB1 varchar(45),
            GAI varchar(45),
            SSXW varchar(45),
            NL varchar(45),
            WB2 varchar(45),
            TIE varchar(45),
            DGC varchar(45),
            CP_DL varchar(45),
            CP_XL varchar(45),
            CP_CLASS varchar(45),
            CP_JF varchar(45),
            CP_COLOR varchar(45),
            DWXDB varchar(45),
            DDDB varchar(45),
            DWXZF varchar(45),
            ZTNL varchar(45),
            DWXWA varchar(45),
            ZL1 varchar(45),
            ZL2 varchar(45),
            ZL3 varchar(45),
            ZL4 varchar(45),        
            PRIMARY KEY (CP_ID)        
             )

        """
    cursor.execute(sql)


def get_csv_data_lsit(filename):
    csv_file = open(filename)  # 打开csv文件
    csv_reader_lines = csv.reader(csv_file)  # 逐行读取csv文件
    date = []  # 创建列表准备接收csv各行数据
    renshu = 0
    for one_line in csv_reader_lines:
        date.append(one_line)  # 将读取的csv分行数据按行存入列表‘date’中
        renshu = renshu + 1
    return date


def insertdb(value_str):
    db = connectdb()
    cursor = db.cursor()
    sql = "INSERT INTO nutrition VALUES %s;" % (value_str)
    print(sql)
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
        print("inserted!")
        return True
    except:
        # Rollback in case there is any error
        print('error!')
        print(value_str)
        db.rollback()
        return False


def insert_csv_to_db(csv_name):
    db = connectdb()
    create_table_nutrition()
    data = get_csv_data_lsit(csv_name)
    # j,k分别是插入成功和失败的记录条数
    failed_list = []
    z = 1
    j = 1
    k = 1
    for i in data:
        str_ = str(tuple(i))
        db = connectdb()  # 连接MySQL数据库
        state = insertdb(str_)
        if state == True:
            print("累计插入数据成功%d条" % (j))
            j += 1
            z += 1
        else:
            print("累计插入数据失败%d条" % (j))
            print("第%d条数据插入失败" % (z))
            k += 1
            failed_list.append(z)
            z += 1
    print("NO." + str(failed_list) + "error!")
    closedb(db)
    return j, k


def querydb_id_list_by_class(class_id):
    db = connectdb()
    cursor = db.cursor()
    sql = "SELECT CP_ID FROM sys.nutrition where CP_CLASS='%s'" % (class_id)
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        result_list = []
        results = cursor.fetchall()
        for i in results:
            result_list.append(i[0])
        closedb(db)
        return result_list
    except:
        print("Error: unable to fecth %s's data" % (class_id))
        closedb(db)


def querydb_jf_by_id(cp_id):
    db = connectdb()
    cursor = db.cursor()
    sql = "SELECT CP_JF FROM sys.nutrition where CP_ID='%s'" % (cp_id)
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        closedb(db)
        return results[0][0]
    except:
        print("Error: unable to fecth %s's data" % (cp_id))
        closedb(db)


def querydb_color_by_id(cp_id):
    db = connectdb()
    cursor = db.cursor()
    sql = "SELECT CP_COLOR FROM sys.nutrition where CP_ID='%s'" % (cp_id)
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        closedb(db)
        return results[0][0]
    except:
        print("Error: unable to fecth %s's data" % (cp_id))
        closedb(db)


def querydb_zl_by_id(cp_id):
    db = connectdb()
    cursor = db.cursor()
    sql = "SELECT ZL1,ZL2,ZL3,ZL4 FROM sys.nutrition where CP_ID='%s'" % (cp_id)
    # print(sql)
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        closedb(db)
        tempt_list = []
        for i in list(results[0]):
            if i != "":
                tempt_list.append(i)
        return tempt_list
    except:
        print("Error: unable to fecth %s's data" % (cp_id))
        closedb(db)


def querydb_class_by_id(cp_id):
    db = connectdb()
    cursor = db.cursor()
    sql = "SELECT CP_CLASS FROM sys.nutrition where CP_ID='%s'" % (cp_id)
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        closedb(db)
        return results[0][0]
    except:
        print("Error: unable to fecth %s's data" % (cp_id))
        closedb(db)


def query_nutrition_by_id(nutrition_type, dish_id):
    db = connectdb()
    cursor = db.cursor()
    sql = "SELECT %s FROM sys.nutrition where CP_ID=%s" % (nutrition_type, dish_id)
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        closedb(db)
        return float(results[0][0])

    except:
        print("Error: unable to fecth %s's data" % (dish_id))
        closedb(db)


def query_all_nutrition_by_id(dish_id):
    db = connectdb()
    cursor = db.cursor()
    sql = "SELECT %s FROM sys.nutrition where CP_ID=%s" % (NTRITION_STR, dish_id)
    # print(sql)
    result = []
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        for i in list(results[0]):
            result.append(float(i))
        return result

    except:
        print("Error: unable to fecth %s's data" % (dish_id))
        closedb(db)


def validate_dish_num():
    for key in RECIPE_MAP:
        print("dish num:%s query num%s" % (RECIPE_MAP[key], str(len(querydb_id_list_by_class(key)))))


def list_item_add(a, b, add_or_sub):
    if add_or_sub == 1:
        c = []
        for i in range(len(a)):
            c1 = a[i] + b[i]
            c.append(c1)
        return c
    if add_or_sub == 0:
        c = []
        for i in range(len(a)):
            c1 = a[i] - b[i]
            c.append(c1)
        return c


def list_multi(list_, rate):
    tempt_list = []
    for i in list_:
        tempt_list.append(i * rate)
    return tempt_list


def calculate_nutrition_list(solver_list):
    # ensure database's consistency
    nutrition_sum_dic = {}
    nutrition_sum_list = []
    for dish_id in solver_list:
        tempt_list = query_all_nutrition_by_id(dish_id)
        if len(nutrition_sum_list) == 0:
            nutrition_sum_list = tempt_list
        else:
            nutrition_sum_list = list_item_add(nutrition_sum_list, tempt_list, 1)
    init = 0
    for nutrition in NTRITION_TYPE:
        nutrition_sum_dic[nutrition] = nutrition_sum_list[init]
        init += 1
    return nutrition_sum_dic, nutrition_sum_list


def calculate_nutrition_week(recipe_week):
    temp_list = []
    for key in recipe_week:
        temp_list = temp_list + recipe_week[key][1]
    nutrition_sum_dic, nutrition_sum_list = calculate_nutrition_list(temp_list)
    return nutrition_sum_dic, nutrition_sum_list


def calculate_nutrition_week_fix(recipe_week):
    nutrition_sum_dic, nutrition_sum_list = calculate_nutrition_week(recipe_week)
    nutrition_sum_dic_ = {}
    D10_list_week = []
    D12_list_week = []
    for key in recipe_week:
        D10_list_week += recipe_week[key][0]["D10"]
        D12_list_week += recipe_week[key][0]["D12"]
    a, b = calculate_nutrition_list(D10_list_week)
    b_update = list_multi(b, -0.3)
    c, d = calculate_nutrition_list(D12_list_week)
    d_update = list_multi(d, 0.2)
    nutrition_sum_list = list_item_add(nutrition_sum_list, d_update, 1)
    nutrition_sum_list = list_item_add(nutrition_sum_list, b_update, 1)
    init = 0
    for nutrition in NTRITION_TYPE:
        nutrition_sum_dic_[nutrition] = nutrition_sum_list[init]
        init += 1
    return nutrition_sum_dic_, nutrition_sum_list


def calculate_nutrition_day_mean(recipe_week):
    nutrition_sum_dic, nutrition_sum_list = calculate_nutrition_week_fix(recipe_week)
    nutrition_sum_dic_day_mean = {}
    nutrition_sum_list_day_mean = []
    for key in nutrition_sum_dic:
        nutrition_sum_dic_day_mean[key] = nutrition_sum_dic[key] / 7
    init = 0
    for key in nutrition_sum_dic_day_mean:
        nutrition_sum_list_day_mean.append(nutrition_sum_dic_day_mean[key])
        init += 1
    return nutrition_sum_dic_day_mean, nutrition_sum_list_day_mean


def filter_validate_percent(nutrition_sum_dic):
    DBZ = nutrition_sum_dic["DBZ"]
    ZF = nutrition_sum_dic["ZF"]
    DWXZF = nutrition_sum_dic["DWXZF"]
    TL = nutrition_sum_dic["TL"]
    DBZ_percent = DBZ * 4 / (DBZ * 4 + ZF * 9 + TL * 4)
    ZF_percent = ZF * 9 / (DBZ * 4 + ZF * 9 + TL * 4)
    TL_percent = TL * 4 / (DBZ * 4 + ZF * 9 + TL * 4)
    result = (DBZ_percent >= 0.12 and DBZ_percent <= 0.15) and \
             (ZF_percent >= 0.2 and ZF_percent <= 0.3) and \
             (TL_percent >= 0.55 and TL_percent <= 0.65) and \
             ((DWXZF / ZF) <= 0.5)
    return result


def filter_A_class_staff_DBZ(nutrition_sum_dic):
    DBZ = nutrition_sum_dic["DBZ"]
    DWXDB = nutrition_sum_dic["DWXDB"]
    DDDB = nutrition_sum_dic["DDDB"]
    percent = (DWXDB + DDDB) / DBZ
    state = (percent >= 0.3) and (percent <= 0.5)
    return state


def filter_BC_class_staff_DBZ(nutrition_sum_dic):
    DBZ = nutrition_sum_dic["DBZ"]
    DWXDB = nutrition_sum_dic["DWXDB"]
    percent = DWXDB / DBZ
    state = (percent >= 0.3) and (percent <= 0.5)
    return state


def filter_BC_class_staff_WA(nutrition_sum_dic):
    WA = nutrition_sum_dic["WA"]
    DWXWA = nutrition_sum_dic["DWXWA"]
    percent = DWXWA / WA
    state = (percent >= 0.333)
    return state


def generate_random_solver():
    solver_dic = {}
    solver_list = []
    for key in RECIPE_MAP:
        dish_id_list = querydb_id_list_by_class(key)
        random_list = []
        list_len = len(dish_id_list)
        if list_len == RECIPE_MAP[key][0]:
            for i in range(RECIPE_MAP[key][1]):
                random_list.append(dish_id_list[random.randint(0, list_len - 1)])
                solver_list.append(dish_id_list[random.randint(0, list_len - 1)])
            solver_dic[key] = random_list
        else:
            print("error dish num!!!!")
    return solver_dic, solver_list


def generate_recipe_week_test():
    recipe_week = {}
    recipe_week["DAY1"] = generate_random_solver()
    recipe_week["DAY2"] = generate_random_solver()
    recipe_week["DAY3"] = generate_random_solver()
    recipe_week["DAY4"] = generate_random_solver()
    recipe_week["DAY5"] = generate_random_solver()
    recipe_week["DAY6"] = generate_random_solver()
    recipe_week["DAY7"] = generate_random_solver()
    return recipe_week


def generate_recipe_week():
    recipe_week = {}
    recipe_week["DAY1"] = generate_random_solver_valid()
    recipe_week["DAY2"] = generate_random_solver_valid()
    recipe_week["DAY3"] = generate_random_solver_valid()
    recipe_week["DAY4"] = generate_random_solver_valid()
    recipe_week["DAY5"] = generate_random_solver_valid()
    recipe_week["DAY6"] = generate_random_solver_valid()
    recipe_week["DAY7"] = generate_random_solver_valid()
    return recipe_week


def generate_one_valid_solver():
    state = False
    init = 1
    while (state is False):
        print("validing NO.%s solver>>>>>>" % (init))
        init += 1
        recipe_week = generate_recipe_week()
        nutrition_sum_dic, nutrition_sum_list = calculate_nutrition_day_mean(recipe_week)

        filter_1 = filter_validate_percent(nutrition_sum_dic)

        state = filter_1

    return recipe_week


def generate_random_solver_valid():
    state = False
    while (state is False):
        solver_dic, solver_list = generate_random_solver()

        CP_CLASS_10_jf_list = []
        CP_CLASS_12_jf_list = []
        CP_CLASS_13_jf_list = []
        for cp_id in solver_list:
            cp_class = querydb_class_by_id(cp_id)
            if cp_class == "D10":
                CP_CLASS_10_jf_list.append(querydb_jf_by_id(cp_id))
            if cp_class == "D12":
                CP_CLASS_12_jf_list.append(querydb_jf_by_id(cp_id))
            if cp_class == "D13":
                CP_CLASS_13_jf_list.append(querydb_jf_by_id(cp_id))
        d = collections.Counter(CP_CLASS_10_jf_list)
        tempt_list = []
        for k in d:
            tempt_list.append(d[k])
        cp_10_jf_set = set(tempt_list)
        state_jf_1 = len(set(CP_CLASS_10_jf_list)) >= 5 or cp_10_jf_set == {2, 3} or cp_10_jf_set == {1, 2, 3}
        state_jf_2 = len(set(CP_CLASS_12_jf_list)) >= 3
        state_jf_3 = len(set(CP_CLASS_13_jf_list)) >= 2
        state_jf = state_jf_1 and state_jf_2 and state_jf_3
        if not state_jf:
            continue

        CP_CLASS_10_zl_list = []
        CP_CLASS_12_zl_list = []
        CP_CLASS_13_zl_list = []
        for cp_id in solver_list:
            cp_class = querydb_class_by_id(cp_id)
            if cp_class == "D10":
                CP_CLASS_10_zl_list += querydb_zl_by_id(cp_id)
            if cp_class == "D12":
                CP_CLASS_12_zl_list += querydb_zl_by_id(cp_id)
            if cp_class == "D13":
                CP_CLASS_13_zl_list += querydb_zl_by_id(cp_id)
        state_zl_1 = len(CP_CLASS_10_zl_list) <= (len(set(CP_CLASS_10_zl_list)) + 3)
        state_zl_2 = len(CP_CLASS_12_zl_list) <= (len(set(CP_CLASS_13_zl_list)) + 2)
        state_zl_3 = len(CP_CLASS_13_zl_list) == len(set(CP_CLASS_13_zl_list))
        state_zl = state_zl_1 and state_zl_2 and state_zl_3
        if not state_zl:
            continue

        CP_COLOR_list = []
        for cp_id in solver_list:
            cp_color = querydb_color_by_id(cp_id)
            CP_COLOR_list.append(cp_color)
        state_color = len(set(CP_COLOR_list)) == 6
        if not state_color:
            continue

        state = state_zl and state_jf and state_color
        if state:
            return solver_dic, solver_list


def test_querydb_nl_by_id(cp_id):
    db = connectdb()
    cursor = db.cursor()
    sql = "SELECT NL,ZTNL FROM sys.nutrition where CP_ID='%s'" % (cp_id)
    # print(sql)
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        closedb(db)
        tempt_list = []
        for i in list(results[0]):
            if i != "":
                tempt_list.append(i)
        return tempt_list
    except:
        print("Error: unable to fecth %s's data" % (cp_id))
        closedb(db)


def fix_week_recipe_dic(week_recipe_dic):
    new_dic = {}
    for k, v in week_recipe_dic.items():
        tempt_dic = {}
        for i, j in v.items():
            tempt_dic[i] = j[0]
        new_dic[k] = tempt_dic
    return new_dic


def create_week_recipe_dic_task(recipe_num):
    week_recipe_dic = {}
    for i in range(recipe_num):
        print("calculating NO.%s week recipe>>>>>" % (i + 1))
        time1 = time.time()
        recipe_week = generate_recipe_week()
        # print(calculate_nutrition_day_mean(recipe_week))
        time2 = time.time()
        print("consume time %s seconds to calculate  calculating NO.%s week recipe" % (str(time2 - time1), i + 1))
        week_recipe_dic["solver" + str(1)] = recipe_week
        time.sleep(45)
        json_file_name = "week_" + str(i+445) + "_recipe.json"
        with open(json_file_name, "w+") as json_file:
            print(json_file_name, "is generating", '.'*40)
            json_file.write(json.dumps(fix_week_recipe_dic(week_recipe_dic)))


if __name__ == '__main__':
    # insert_csv_to_db("nutrition.csv")
    # 第一行没插入
    create_week_recipe_dic_task(2000)
    # connectdb()
